const mysql = require('mysql2/promise');

async function initCloudDatabase() {
  console.log('正在初始化云服务器数据库...');
  
  try {
    // 创建连接（不指定数据库，先创建数据库）
    const connection = await mysql.createConnection({
      host: '8.149.243.80',
      port: 3307,
      user: 'root',
      password: '123456'
    });

    console.log('✅ 连接到MySQL服务器成功');

    // 创建数据库
    await connection.execute('CREATE DATABASE IF NOT EXISTS mqtt_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
    console.log('✅ 数据库 mqtt_app 创建成功');

    // 使用数据库
    await connection.execute('USE mqtt_app');
    console.log('✅ 切换到 mqtt_app 数据库');

    // 创建logs表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS logs (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        timestamp VARCHAR(32) NOT NULL,
        type VARCHAR(32) NOT NULL,
        topic VARCHAR(255) DEFAULT NULL,
        message MEDIUMTEXT,
        qos TINYINT DEFAULT 0,
        retained TINYINT DEFAULT 0,
        source VARCHAR(32) DEFAULT NULL,
        client_id VARCHAR(128) NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_ts (timestamp),
        INDEX idx_type (type),
        INDEX idx_topic (topic),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `);
    console.log('✅ logs表创建成功');

    // 创建device_status表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS device_status (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        device_id VARCHAR(100) NOT NULL,
        topic VARCHAR(255) NOT NULL,
        status VARCHAR(50) DEFAULT 'offline',
        last_message TEXT,
        last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        message_count BIGINT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uk_device_topic (device_id, topic),
        INDEX idx_device_id (device_id),
        INDEX idx_status (status),
        INDEX idx_last_seen (last_seen)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `);
    console.log('✅ device_status表创建成功');

    // 创建message_stats表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS message_stats (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        stat_date DATE NOT NULL,
        stat_hour TINYINT NOT NULL,
        topic VARCHAR(255) NOT NULL,
        message_count BIGINT DEFAULT 0,
        total_size BIGINT DEFAULT 0,
        avg_size DECIMAL(10,2) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uk_date_hour_topic (stat_date, stat_hour, topic),
        INDEX idx_stat_date (stat_date),
        INDEX idx_topic (topic)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    `);
    console.log('✅ message_stats表创建成功');

    // 检查表结构
    const [tables] = await connection.execute('SHOW TABLES');
    console.log('✅ 数据库表列表:', tables.map(t => t[Object.keys(t)[0]]));

    await connection.end();
    console.log('✅ 数据库连接已关闭');
    console.log('');
    console.log('🎉 云服务器数据库初始化完成！');

  } catch (error) {
    console.error('❌ 数据库初始化失败:');
    console.error('错误信息:', error.message);
    console.error('错误代码:', error.code);
    console.log('');
    console.log('请检查以下配置:');
    console.log('1. 云服务器IP地址是否正确');
    console.log('2. MySQL端口3307是否正确');
    console.log('3. 用户名和密码是否正确');
    console.log('4. 云服务器防火墙是否开放3307端口');
    console.log('5. MySQL是否允许远程连接');
  }
}

initCloudDatabase();
